In [1]:
import pandas as pd
import numpy as np
import folium
import pgeocode
from folium.plugins import HeatMap
# =====================================================
# 0. LOAD + CLEAN PIPELINE DATA
# =====================================================
pipeline_xlsx = "Copy of Housing_Pipeline_Long_List_External_Hackathon.xlsx"
df_pipe = pd.read_excel(pipeline_xlsx)
cols_pipe = df_pipe.columns.tolist()
print("Raw columns:", cols_pipe, "\n")
def pick_col(substring: str) -> str:
"""Find the first column whose name contains the substring (case-insensitive)."""
for c in cols_pipe:
if substring.lower() in c.lower():
return c
raise ValueError(f"Column containing '{substring}' not found")
ref_col = pick_col("Ref")
proj_name_col = pick_col("Project Name")
sponsor_col = pick_col("Sponsor")
la_col = pick_col("LA")
stage_col = pick_col("Pipeline First Cut")
postcode_col = pick_col("Postcode")
dev_col = pick_col("Developer")
summary_col = pick_col("One Line Summary")
units_col = pick_col("Number Of Units")
size_col = pick_col("Site Size") # Site Size (hectares)
pipeline = df_pipe[
[
ref_col,
proj_name_col,
sponsor_col,
la_col,
stage_col,
postcode_col,
dev_col,
summary_col,
units_col,
size_col,
]
].copy()
pipeline = pipeline.rename(
columns={
ref_col: "ref",
proj_name_col: "project_name",
sponsor_col: "sponsor",
la_col: "la",
stage_col: "pipeline_stage",
postcode_col: "postcode",
dev_col: "developer",
summary_col: "summary",
units_col: "units",
size_col: "site_size_ha",
}
)
# numeric conversions
pipeline["units"] = pd.to_numeric(pipeline["units"], errors="coerce")
pipeline["site_size_ha"] = pd.to_numeric(pipeline["site_size_ha"], errors="coerce")
print("Cleaned columns:", pipeline.columns.tolist())
print(pipeline[["project_name", "la", "units", "site_size_ha"]].head(), "\n")
# =====================================================
# 1. GEOCODE POSTCODES WITH pgeocode
# =====================================================
nomi = pgeocode.Nominatim("GB")
def geocode_one(pc: str):
if pd.isna(pc):
return np.nan, np.nan
pc = str(pc).strip()
if not pc:
return np.nan, np.nan
rec = nomi.query_postal_code(pc)
lat = rec.get("latitude", np.nan)
lon = rec.get("longitude", np.nan)
return lat, lon
pipeline["postcode_clean"] = pipeline["postcode"].astype(str).str.strip()
pipeline[["lat", "lon"]] = pipeline["postcode_clean"].apply(
lambda x: pd.Series(geocode_one(x))
)
pipeline_geo = pipeline.dropna(subset=["lat", "lon"]).reset_index(drop=True)
print("Geocoded sites:", len(pipeline_geo), "of", len(pipeline), "\n")
# =====================================================
# 2. CREATE BANDS (UNITS + SITE SIZE)
# =====================================================
units_bins = [0, 20, 50, 100, 200, np.inf]
units_labels = ["0–20 homes", "21–50 homes", "51–100 homes",
"101–200 homes", "200+ homes"]
pipeline_geo["units_band"] = pd.cut(
pipeline_geo["units"].fillna(0),
bins=units_bins,
labels=units_labels,
include_lowest=True
)
size_bins = [0, 0.5, 2, 5, 10, np.inf]
size_labels = ["<0.5 ha", "0.5–2 ha", "2–5 ha", "5–10 ha", "10+ ha"]
pipeline_geo["size_band"] = pd.cut(
pipeline_geo["site_size_ha"].fillna(0),
bins=size_bins,
labels=size_labels,
include_lowest=True
)
print("Units bands:\n", pipeline_geo["units_band"].value_counts(), "\n")
print("Site size bands:\n", pipeline_geo["size_band"].value_counts(), "\n")
# =====================================================
# 3. HEATMAP + BUBBLE MAP (UNITS + SITE SIZE)
# =====================================================
print("Rows in pipeline_geo:", len(pipeline_geo))
centre_lat = pipeline_geo["lat"].mean()
centre_lon = pipeline_geo["lon"].mean()
m_all = folium.Map(
location=[centre_lat, centre_lon],
zoom_start=9,
tiles="OpenStreetMap"
)
# 1) Bubble layers by UNITS band
stage_colors = {
"Short": "green",
"Medium": "orange",
"Long": "red"
}
size_colors = {
"<0.5 ha": "#999999",
"0.5–2 ha": "#377eb8",
"2–5 ha": "#4daf4a",
"5–10 ha": "#984ea3",
"10+ ha": "#e41a1c"
}
fg_by_units = {lab: folium.FeatureGroup(name=f"Projects: {lab}", show=True)
for lab in units_labels}
for _, row in pipeline_geo.iterrows():
ub = row["units_band"]
if pd.isna(ub):
continue
ub = str(ub)
fg = fg_by_units[ub]
units = row["units"] if not np.isnan(row["units"]) else 0
radius = 4 + (np.sqrt(units) if units > 0 else 0)
stage = str(row["pipeline_stage"]).strip()
fill_color = stage_colors.get(stage, "blue")
sb = row["size_band"]
border_color = size_colors.get(sb, "#333333")
desc = (
f"<b>{row['project_name']}</b><br>"
f"{int(units) if units > 0 else 'Unknown'} homes in {row['la']}<br>"
f"Stage: {row['pipeline_stage']} | Site size: {row['site_size_ha']} ha ({sb})<br>"
f"Developer: {row['developer']}<br>"
f"Postcode: {row['postcode']}<br><br>"
f"{row['summary']}"
)
folium.CircleMarker(
location=[row["lat"], row["lon"]],
radius=radius,
color=border_color,
weight=2,
fill=True,
fill_opacity=0.85,
fill_color=fill_color,
tooltip=folium.Tooltip(desc)
).add_to(fg)
for fg in fg_by_units.values():
fg.add_to(m_all)
# 2) HEATMAP – NUMBER OF HOMES
units_data = pipeline_geo[["lat", "lon", "units"]].dropna()
if not units_data.empty:
units_data = units_data[units_data["units"] > 0].copy()
units_data["w"] = units_data["units"] / units_data["units"].max()
fg_units_heat = folium.FeatureGroup(
name="Heat: number of homes", show=False
)
HeatMap(
data=list(zip(units_data["lat"], units_data["lon"], units_data["w"])),
radius=25,
blur=18,
min_opacity=0.25,
max_zoom=12
).add_to(fg_units_heat)
fg_units_heat.add_to(m_all)
# 3) HEATMAP – SITE SIZE (HECTARES)
size_data = pipeline_geo[["lat", "lon", "site_size_ha"]].dropna()
if not size_data.empty:
size_data = size_data[size_data["site_size_ha"] > 0].copy()
size_data["w"] = size_data["site_size_ha"] / size_data["site_size_ha"].max()
fg_size_heat = folium.FeatureGroup(
name="Heat: site size (ha)", show=False
)
HeatMap(
data=list(zip(size_data["lat"], size_data["lon"], size_data["w"])),
radius=25,
blur=18,
min_opacity=0.25,
max_zoom=12
).add_to(fg_size_heat)
fg_size_heat.add_to(m_all)
# 4) LAYER CONTROL
folium.LayerControl(collapsed=False).add_to(m_all)
m_all
Raw columns: ['Mapping Ref', 'Ref', 'Project Name ', 'Sponsor ', 'LA', 'Pipeline First Cut ', 'Known HE involvement?', 'Postcode', 'Developer ', 'One Line Summary ', 'Number Of Units ', 'Site Size (hectares)', 'Greenfield/Brownfield', 'Place-based Pipeline Y/N', 'Place-based Pipeline Link', 'Mix', 'SPP Priority Area Y/N', 'SPP Priority Area', 'Planning Status ', 'Developer Status ', 'Land Ownership Type ', 'Procurement Route ', 'Enabling Works Start', 'Housing Start', 'Project Completion Date ', 'Market Failure Type', 'Intervention Required?', 'Revenue / Development Ask ', 'Intervention Type', 'Constraints', 'Column1', 'Column2', 'Column3', 'Column4', 'Column5', 'Column6', 'Column7', 'Column8', 'Column9']
Cleaned columns: ['ref', 'project_name', 'sponsor', 'la', 'pipeline_stage', 'postcode', 'developer', 'summary', 'units', 'site_size_ha']
project_name la units site_size_ha
0 Hawthorne Grove Halton 22.0 0.29
1 Heysham Road Sefton 54.0 1.50
2 Alder Hey Liverpool 8.0 NaN
3 Ullswater Avenue St Helens 33.0 1.49
4 Carr Lane, Prescot Knowsley 93.0 3.40
Geocoded sites: 242 of 341
Units bands:
units_band
51–100 homes 58
200+ homes 51
21–50 homes 49
0–20 homes 42
101–200 homes 42
Name: count, dtype: int64
Site size bands:
size_band
<0.5 ha 98
0.5–2 ha 68
2–5 ha 34
10+ ha 26
5–10 ha 16
Name: count, dtype: int64
Rows in pipeline_geo: 242
Out[1]:
Make this Notebook Trusted to load map: File -> Trust Notebook
In [2]:
m_all.save("pipeline_projects_map.html")
In [4]:
import json
from pathlib import Path
import folium
import geopandas as gpd
import numpy as np
import pandas as pd
import pgeocode
from branca.element import Element
from folium.plugins import HeatMap
# =====================================================
# 0. CONFIG & FILE PATHS
# =====================================================
bound_path = Path("CAUTH_MAY_2025_EN_BSC_1271049543973882786.geojson")
lsoa_path = Path("Lower_layer_Super_Output_Areas_December_2021_Boundaries_EW_BSC_V4_-4299016806856585929.geojson")
fuel_excel_path = Path("Sub-regional_fuel_poverty_statistics_2023.xlsx")
imd_excel_path = Path("Deprivation_2025.xlsx")
brownfield_path = Path("brownfield-land.geojson")
police_dir = Path("police_data")
pipeline_xlsx = Path("Copy of Housing_Pipeline_Long_List_External_Hackathon.xlsx")
print(">>> STEP 1/4 – Build vulnerability (LSOA + brownfield) data")
# =====================================================
# 1. VULNERABILITY DATA (LSOA + BROWNFIELD)
# =====================================================
# 1.1 LCR boundary
bound_gdf = gpd.read_file(bound_path)
if bound_gdf.crs is not None and bound_gdf.crs.to_epsg() != 4326:
bound_gdf = bound_gdf.to_crs(epsg=4326)
lcr = bound_gdf[bound_gdf["CAUTH25NM"] == "Liverpool City Region"].copy()
if lcr.empty:
raise ValueError("Cannot find 'Liverpool City Region' in boundary file")
try:
centre = lcr.geometry.union_all().centroid
except AttributeError:
centre = lcr.geometry.unary_union.centroid
centre_lat, centre_lon = centre.y, centre.x
# 1.2 LSOA polygons
lsoa_gdf = gpd.read_file(lsoa_path)
if lsoa_gdf.crs is not None and lsoa_gdf.crs.to_epsg() != 4326:
lsoa_gdf = lsoa_gdf.to_crs(epsg=4326)
lsoa_lcr = gpd.sjoin(
lsoa_gdf, lcr[["geometry"]], how="inner", predicate="intersects"
).drop(columns=["index_right"])
print(f" -> LSOAs inside LCR: {len(lsoa_lcr)}")
valid_lsoa_codes = set(lsoa_lcr["LSOA21CD"].unique())
# 1.3 Fuel poverty
try:
fuel_df = pd.read_excel(fuel_excel_path, sheet_name="Table 4", header=2)
fuel_df = fuel_df.rename(
columns={
"LSOA Code": "LSOA21CD",
"Proportion of households fuel poor (%)": "Fuel_Poverty_Rate",
}
)
fuel_df["Fuel_Poverty_Rate"] = pd.to_numeric(
fuel_df["Fuel_Poverty_Rate"], errors="coerce"
)
except Exception as e:
print(" !! Fuel data problem:", e)
fuel_df = pd.DataFrame(columns=["LSOA21CD", "Fuel_Poverty_Rate"])
# 1.4 IMD (decile)
try:
imd_df = pd.read_excel(imd_excel_path, sheet_name="IMD25", header=0)
imd_decile_col = next(
c for c in imd_df.columns if "IMD" in str(c) and "Decile" in str(c)
)
imd_df = imd_df.rename(
columns={
"LSOA code (2021)": "LSOA21CD",
imd_decile_col: "IMD_Decile",
}
)
imd_df["IMD_Decile"] = pd.to_numeric(imd_df["IMD_Decile"], errors="coerce")
except Exception as e:
print(" !! IMD data problem:", e)
imd_df = pd.DataFrame(columns=["LSOA21CD", "IMD_Decile"])
# 1.5 Police data: street crime + stop-and-search (uses ./police_data)
street_counts = {}
stop_search_points = []
all_csvs = list(police_dir.rglob("*.csv"))
for csv_file in all_csvs:
fname = csv_file.name.lower()
try:
if "street" in fname:
df = pd.read_csv(csv_file, usecols=["LSOA code"])
df = df[df["LSOA code"].isin(valid_lsoa_codes)]
counts = df["LSOA code"].value_counts()
for code, count in counts.items():
street_counts[code] = street_counts.get(code, 0) + count
elif "stop-and-search" in fname:
df = pd.read_csv(csv_file, usecols=["Latitude", "Longitude"])
df = df.dropna(subset=["Latitude", "Longitude"])
# Rough bounding box for LCR area
mask = df["Latitude"].between(53.0, 54.0) & df["Longitude"].between(-3.5, -2.0)
df_trimmed = df[mask]
if not df_trimmed.empty:
stop_search_points.append(df_trimmed)
except Exception:
continue
df_street = pd.DataFrame(list(street_counts.items()), columns=["LSOA21CD", "Crime_Count"])
df_stop_agg = pd.DataFrame(columns=["LSOA21CD", "StopSearch_Count"])
if stop_search_points:
all_stops = pd.concat(stop_search_points, ignore_index=True)
gdf_stops = gpd.GeoDataFrame(
all_stops,
geometry=gpd.points_from_xy(all_stops.Longitude, all_stops.Latitude),
crs="EPSG:4326",
)
stops_with_lsoa = gpd.sjoin(
gdf_stops, lsoa_lcr[["LSOA21CD", "geometry"]], predicate="within"
)
stop_counts = stops_with_lsoa["LSOA21CD"].value_counts().reset_index()
stop_counts.columns = ["LSOA21CD", "StopSearch_Count"]
df_stop_agg = stop_counts
# 1.6 Merge metrics into master LSOA GeoDataFrame
master_gdf = lsoa_lcr.merge(fuel_df, on="LSOA21CD", how="left")
master_gdf = master_gdf.merge(imd_df, on="LSOA21CD", how="left")
master_gdf = master_gdf.merge(df_street, on="LSOA21CD", how="left")
master_gdf = master_gdf.merge(df_stop_agg, on="LSOA21CD", how="left")
master_gdf["Fuel_Poverty_Rate"] = master_gdf["Fuel_Poverty_Rate"].fillna(0)
master_gdf["IMD_Decile"] = master_gdf["IMD_Decile"].fillna(10)
master_gdf["Crime_Count"] = master_gdf["Crime_Count"].fillna(0)
master_gdf["StopSearch_Count"] = master_gdf["StopSearch_Count"].fillna(0)
# Normalisation
print(">>> STEP 2/4 – Normalising metrics")
f_min, f_max = master_gdf["Fuel_Poverty_Rate"].min(), master_gdf["Fuel_Poverty_Rate"].max()
master_gdf["norm_fuel"] = (master_gdf["Fuel_Poverty_Rate"] - f_min) / (
(f_max - f_min) + 1e-9
)
master_gdf["norm_imd"] = (11 - master_gdf["IMD_Decile"]) / 10.0 # 1 = most deprived
c_max = master_gdf["Crime_Count"].quantile(0.95)
if c_max <= 0:
master_gdf["norm_crime"] = 0
else:
master_gdf["norm_crime"] = (master_gdf["Crime_Count"] / c_max).clip(upper=1.0)
s_max = master_gdf["StopSearch_Count"].quantile(0.95)
if s_max <= 0:
master_gdf["norm_stop"] = 0
else:
master_gdf["norm_stop"] = (master_gdf["StopSearch_Count"] / s_max).clip(upper=1.0)
cols_round = ["norm_fuel", "norm_imd", "norm_crime", "norm_stop", "Fuel_Poverty_Rate"]
master_gdf[cols_round] = master_gdf[cols_round].round(3)
print(" -> Simplifying geometries")
master_gdf.geometry = master_gdf.geometry.simplify(tolerance=0.0001, preserve_topology=True)
# 1.7 Brownfield data
print(">>> STEP 3/4 – Preparing brownfield sites")
brownfield = gpd.read_file(brownfield_path).to_crs(epsg=4326)
bf_joined = gpd.sjoin(
brownfield, master_gdf[["geometry", "LSOA21CD"]], predicate="within"
)
bf_joined["hectares"] = pd.to_numeric(bf_joined["hectares"], errors="coerce").fillna(0)
bf_data_list = []
for _, row in bf_joined.iterrows():
if row.geometry.is_empty:
continue
bf_data_list.append(
{
"lat": row.geometry.centroid.y,
"lng": row.geometry.centroid.x,
"ha": round(row["hectares"], 2),
"name": row.get("name", row.get("SiteName", "Unknown Site")),
"address": row.get("site-address", "No address provided"),
"status": row.get("planning-permission-status", "N/A"),
"dwellings": row.get("minimum-net-dwellings", "N/A"),
"lsoa": row["LSOA21CD"],
}
)
geojson_str = master_gdf.to_json()
# =====================================================
# 2. PIPELINE PROJECTS (BUBBLES + HEATMAPS)
# =====================================================
print(">>> STEP 4/4 – Loading housing pipeline")
df_pipe = pd.read_excel(pipeline_xlsx)
cols_pipe = df_pipe.columns.tolist()
def pick_col(substring: str) -> str:
for c in cols_pipe:
if substring.lower() in c.lower():
return c
raise ValueError(f"Column containing '{substring}' not found")
ref_col = pick_col("Ref")
proj_name_col = pick_col("Project Name")
sponsor_col = pick_col("Sponsor")
la_col = pick_col("LA")
stage_col = pick_col("Pipeline First Cut")
postcode_col = pick_col("Postcode")
dev_col = pick_col("Developer")
summary_col = pick_col("One Line Summary")
units_col = pick_col("Number Of Units")
size_col = pick_col("Site Size")
pipeline = df_pipe[
[
ref_col,
proj_name_col,
sponsor_col,
la_col,
stage_col,
postcode_col,
dev_col,
summary_col,
units_col,
size_col,
]
].copy()
pipeline = pipeline.rename(
columns={
ref_col: "ref",
proj_name_col: "project_name",
sponsor_col: "sponsor",
la_col: "la",
stage_col: "pipeline_stage",
postcode_col: "postcode",
dev_col: "developer",
summary_col: "summary",
units_col: "units",
size_col: "site_size_ha",
}
)
pipeline["units"] = pd.to_numeric(pipeline["units"], errors="coerce")
pipeline["site_size_ha"] = pd.to_numeric(pipeline["site_size_ha"], errors="coerce")
# Geocode postcodes with pgeocode
nomi = pgeocode.Nominatim("GB")
def geocode_one(pc: str):
if pd.isna(pc):
return np.nan, np.nan
pc = str(pc).strip()
if not pc:
return np.nan, np.nan
rec = nomi.query_postal_code(pc)
lat = rec.get("latitude", np.nan)
lon = rec.get("longitude", np.nan)
return lat, lon
pipeline["postcode_clean"] = pipeline["postcode"].astype(str).str.strip()
pipeline[["lat", "lon"]] = pipeline["postcode_clean"].apply(
lambda x: pd.Series(geocode_one(x))
)
pipeline_geo = pipeline.dropna(subset=["lat", "lon"]).reset_index(drop=True)
print(" -> Geocoded sites:", len(pipeline_geo))
# Bands
units_bins = [0, 20, 50, 100, 200, np.inf]
units_labels = ["0–20 homes", "21–50 homes", "51–100 homes", "101–200 homes", "200+ homes"]
pipeline_geo["units_band"] = pd.cut(
pipeline_geo["units"].fillna(0),
bins=units_bins,
labels=units_labels,
include_lowest=True,
)
size_bins = [0, 0.5, 2, 5, 10, np.inf]
size_labels = ["<0.5 ha", "0.5–2 ha", "2–5 ha", "5–10 ha", "10+ ha"]
pipeline_geo["size_band"] = pd.cut(
pipeline_geo["site_size_ha"].fillna(0),
bins=size_bins,
labels=size_labels,
include_lowest=True,
)
# =====================================================
# 3. BUILD ONE COMBINED MAP
# =====================================================
print(">>> BUILDING COMBINED MAP")
m = folium.Map(
location=[centre_lat, centre_lon],
zoom_start=11,
tiles="CartoDB positron"
)
# Custom panes so polygons are under points, etc.
folium.map.CustomPane("poly_pane", z_index=400).add_to(m)
folium.map.CustomPane("brownfield_pane", z_index=650).add_to(m)
folium.map.CustomPane("projects_pane", z_index=700).add_to(m)
# -----------------------------------------------------
# 3.1 Pipeline project bubbles (FeatureGroups by units)
# -----------------------------------------------------
stage_colors = {"Short": "green", "Medium": "orange", "Long": "red"}
size_colors = {
"<0.5 ha": "#999999",
"0.5–2 ha": "#377eb8",
"2–5 ha": "#4daf4a",
"5–10 ha": "#984ea3",
"10+ ha": "#e41a1c",
}
fg_by_units = {
lab: folium.FeatureGroup(name=f"Projects: {lab}", show=True)
for lab in units_labels
}
for _, row in pipeline_geo.iterrows():
ub = row["units_band"]
if pd.isna(ub):
continue
ub = str(ub)
fg = fg_by_units[ub]
units = row["units"] if not np.isnan(row["units"]) else 0
radius = 4 + (np.sqrt(units) if units > 0 else 0)
stage = str(row["pipeline_stage"]).strip()
fill_color = stage_colors.get(stage, "blue")
sb = row["size_band"]
border_color = size_colors.get(sb, "#333333")
desc = (
f"<b>{row['project_name']}</b><br>"
f"{int(units) if units > 0 else 'Unknown'} homes in {row['la']}<br>"
f"Stage: {row['pipeline_stage']} | Site size: {row['site_size_ha']} ha ({sb})<br>"
f"Developer: {row['developer']}<br>"
f"Postcode: {row['postcode']}<br><br>"
f"{row['summary']}"
)
folium.CircleMarker(
location=[row["lat"], row["lon"]],
radius=radius,
color=border_color,
weight=2,
fill=True,
fill_opacity=0.85,
fill_color=fill_color,
tooltip=folium.Tooltip(desc),
pane="projects_pane",
).add_to(fg)
for fg in fg_by_units.values():
fg.add_to(m)
# -----------------------------------------------------
# 3.2 Heatmaps – units & site size
# -----------------------------------------------------
units_data = pipeline_geo[["lat", "lon", "units"]].dropna()
if not units_data.empty:
units_data = units_data[units_data["units"] > 0].copy()
units_data["w"] = units_data["units"] / units_data["units"].max()
fg_units_heat = folium.FeatureGroup(name="Heat: number of homes", show=False)
HeatMap(
data=list(zip(units_data["lat"], units_data["lon"], units_data["w"])),
radius=25,
blur=18,
min_opacity=0.25,
max_zoom=12,
).add_to(fg_units_heat)
fg_units_heat.add_to(m)
size_data = pipeline_geo[["lat", "lon", "site_size_ha"]].dropna()
if not size_data.empty:
size_data = size_data[size_data["site_size_ha"] > 0].copy()
size_data["w"] = size_data["site_size_ha"] / size_data["site_size_ha"].max()
fg_size_heat = folium.FeatureGroup(name="Heat: site size (ha)", show=False)
HeatMap(
data=list(zip(size_data["lat"], size_data["lon"], size_data["w"])),
radius=25,
blur=18,
min_opacity=0.25,
max_zoom=12,
).add_to(fg_size_heat)
fg_size_heat.add_to(m)
# -----------------------------------------------------
# 3.3 Inject custom UI for FOUR separate choropleths
# -----------------------------------------------------
custom_ui = f"""
<style>
#controls {{
position: absolute; bottom: 30px; right: 10px; width: 320px;
background: rgba(255, 255, 255, 0.96); backdrop-filter: blur(5px);
padding: 15px; border-radius: 10px; box-shadow: 0 4px 15px rgba(0,0,0,0.25);
z-index: 1000; font-family: 'Segoe UI', Tahoma, Geneva, Verdana, sans-serif;
}}
#controls h4 {{ margin: 0 0 10px 0; color: #222; }}
.metric-buttons {{
display: grid;
grid-template-columns: repeat(2, 1fr);
grid-gap: 6px;
margin-bottom: 8px;
}}
.metric-btn {{
padding: 6px 8px;
border-radius: 6px;
border: 1px solid #ddd;
background: #f7f7f7;
cursor: pointer;
font-size: 12px;
font-weight: 600;
color: #444;
}}
.metric-btn.active {{
background: #222;
color: #fff;
border-color: #222;
}}
.legend-gradient {{
height: 10px; background: linear-gradient(to right, #ffffff, #fee5d9, #de2d26, #a50f15);
border-radius: 5px; margin-top: 8px; border: 1px solid #ccc;
}}
.legend-labels {{ display: flex; justify-content: space-between; font-size: 10px; color: #666; margin-top: 2px; }}
.popup-table td {{ padding: 2px 5px; border-bottom: 1px solid #eee; }}
.popup-header {{ margin: 0 0 5px 0; border-bottom: 2px solid #555; padding-bottom: 3px; font-size: 14px; color: #333; }}
</style>
<div id="controls">
<h4>Neighbourhood Vulnerability</h4>
<div class="metric-buttons">
<button class="metric-btn active" data-metric="none">No Choropleth</button>
<button class="metric-btn" data-metric="fuel">Fuel Poverty</button>
<button class="metric-btn" data-metric="imd">IMD Deprivation</button>
<button class="metric-btn" data-metric="crime">Crime Density</button>
<button class="metric-btn" data-metric="stop">Stop & Search</button>
</div>
<div style="font-size:11px;color:#555;margin-bottom:4px;">
Showing: <b><span id="active_metric_label">None</span></b>
</div>
<div class="legend-gradient"></div>
<div class="legend-labels"><span>Lower</span><span>Higher</span></div>
</div>
<script>
// Data from Python
var lsoaData = {geojson_str};
var brownfieldData = {json.dumps(bf_data_list)};
var mapInstance = null;
var geoLayers = {{}};
var bfLayerGroup = null;
// ------------ Colour scales for each metric ------------
function getColorFuel(v) {{
if (isNaN(v)) return '#cccccc';
if (v > 0.85) return '#cb181d';
if (v > 0.6) return '#fb6a4a';
if (v > 0.3) return '#fcae91';
if (v > 0.0) return '#fee5d9';
return '#ffffff';
}}
function getColorIMD(v) {{
if (isNaN(v)) return '#cccccc';
if (v > 0.85) return '#54278f';
if (v > 0.6) return '#756bb1';
if (v > 0.3) return '#9e9ac8';
if (v > 0.0) return '#dadaeb';
return '#ffffff';
}}
function getColorCrime(v) {{
if (isNaN(v)) return '#cccccc';
if (v > 0.85) return '#08519c';
if (v > 0.6) return '#3182bd';
if (v > 0.3) return '#6baed6';
if (v > 0.0) return '#deebf7';
return '#ffffff';
}}
function getColorStop(v) {{
if (isNaN(v)) return '#cccccc';
if (v > 0.85) return '#006d2c';
if (v > 0.6) return '#31a354';
if (v > 0.3) return '#74c476';
if (v > 0.0) return '#e5f5e0';
return '#ffffff';
}}
function findFoliumMap() {{
for (var key in window) {{
if (window[key] instanceof L.Map) return window[key];
}}
return null;
}}
// ------------ Initialise map overlays ------------
function initMap() {{
mapInstance = findFoliumMap();
if (!mapInstance) {{
setTimeout(initMap, 200);
return;
}}
function onEachLSOA(feature, layer) {{
var p = feature.properties;
var content = `
<div style="font-family:sans-serif;font-size:12px;min-width:220px;">
<h4 class="popup-header">${{p.LSOA21NM}}</h4>
<table class="popup-table" style="width:100%;border-collapse:collapse;">
<tr><td style="color:#666;">Code</td><td>${{p.LSOA21CD}}</td></tr>
<tr><td>Fuel Poverty</td><td><b>${{p.Fuel_Poverty_Rate}}%</b> <small>(${{p.norm_fuel}})</small></td></tr>
<tr><td>IMD Decile</td><td><b>${{p.IMD_Decile}}</b> <small>(${{p.norm_imd}})</small></td></tr>
<tr><td>Crimes</td><td><b>${{p.Crime_Count}}</b> <small>(${{p.norm_crime}})</small></td></tr>
<tr><td>Stop & Search</td><td><b>${{p.StopSearch_Count}}</b> <small>(${{p.norm_stop}})</small></td></tr>
</table>
</div>
`;
layer.bindPopup(content);
layer.bindTooltip(p.LSOA21NM, {{sticky:true}});
}}
// Create four separate GeoJSON layers, one per metric
geoLayers['fuel'] = L.geoJson(lsoaData, {{
pane: 'poly_pane',
style: function(feature) {{
var v = feature.properties.norm_fuel || 0;
return {{
fillColor: getColorFuel(v),
weight: 0.6,
color: '#777',
opacity: 0.6,
fillOpacity: 0.7
}};
}},
onEachFeature: onEachLSOA
}});
geoLayers['imd'] = L.geoJson(lsoaData, {{
pane: 'poly_pane',
style: function(feature) {{
var v = feature.properties.norm_imd || 0;
return {{
fillColor: getColorIMD(v),
weight: 0.6,
color: '#777',
opacity: 0.6,
fillOpacity: 0.7
}};
}},
onEachFeature: onEachLSOA
}});
geoLayers['crime'] = L.geoJson(lsoaData, {{
pane: 'poly_pane',
style: function(feature) {{
var v = feature.properties.norm_crime || 0;
return {{
fillColor: getColorCrime(v),
weight: 0.6,
color: '#777',
opacity: 0.6,
fillOpacity: 0.7
}};
}},
onEachFeature: onEachLSOA
}});
geoLayers['stop'] = L.geoJson(lsoaData, {{
pane: 'poly_pane',
style: function(feature) {{
var v = feature.properties.norm_stop || 0;
return {{
fillColor: getColorStop(v),
weight: 0.6,
color: '#777',
opacity: 0.6,
fillOpacity: 0.7
}};
}},
onEachFeature: onEachLSOA
}});
// Brownfield points: ALWAYS BROWN
bfLayerGroup = L.layerGroup().addTo(mapInstance);
brownfieldData.forEach(function(site) {{
var circle = L.circleMarker([site.lat, site.lng], {{
pane: 'brownfield_pane',
radius: 4 + (site.ha * 0.4),
fillColor: '#8c510a', // brown
color: '#4d2600',
weight: 1,
opacity: 1,
fillOpacity: 0.95
}});
var bfContent = `
<div style="font-family:sans-serif;font-size:12px;min-width:200px;">
<h4 class="popup-header">${{site.name}}</h4>
<div style="margin-bottom:8px;color:#555;">${{site.address}}</div>
<table class="popup-table" style="width:100%;">
<tr><td><strong>Size:</strong></td><td>${{site.ha}} ha</td></tr>
<tr><td><strong>Dwellings:</strong></td><td>${{site.dwellings}}</td></tr>
<tr><td><strong>Status:</strong></td><td>${{site.status}}</td></tr>
</table>
</div>
`;
circle.bindPopup(bfContent);
bfLayerGroup.addLayer(circle);
}});
setupButtons();
}}
// ------------ Button logic: toggle which choropleth is visible ------------
function setActiveMetric(metric) {{
// Remove all metric layers
Object.keys(geoLayers).forEach(function(key) {{
var layer = geoLayers[key];
if (mapInstance.hasLayer(layer)) {{
mapInstance.removeLayer(layer);
}}
}});
// Update active button class
var buttons = document.querySelectorAll('#controls .metric-btn');
buttons.forEach(function(btn) {{
btn.classList.toggle('active', btn.getAttribute('data-metric') === metric);
}});
// Update label
var label = document.getElementById('active_metric_label');
var mapping = {{
'none': 'None',
'fuel': 'Fuel Poverty',
'imd': 'IMD Deprivation',
'crime': 'Crime Density',
'stop': 'Stop & Search'
}};
label.textContent = mapping[metric] || 'None';
// If "none" selected, just leave polygons off
if (metric === 'none') {{
return;
}}
// Add the chosen layer
var chosen = geoLayers[metric];
if (chosen) {{
chosen.addTo(mapInstance);
}}
}}
function setupButtons() {{
var buttons = document.querySelectorAll('#controls .metric-btn');
buttons.forEach(function(btn) {{
btn.addEventListener('click', function() {{
var metric = this.getAttribute('data-metric');
setActiveMetric(metric);
}});
}});
// start with "none"
setActiveMetric('none');
}}
initMap();
</script>
"""
m.get_root().html.add_child(Element(custom_ui))
# Standard layer control for project + heatmap layers
folium.LayerControl(collapsed=False).add_to(m)
# Save to HTML and display in notebook
m.save("LCR_vulnerability_pipeline_four_choropleths.html")
m
>>> STEP 1/4 – Build vulnerability (LSOA + brownfield) data -> LSOAs inside LCR: 1040 >>> STEP 2/4 – Normalising metrics -> Simplifying geometries >>> STEP 3/4 – Preparing brownfield sites >>> STEP 4/4 – Loading housing pipeline -> Geocoded sites: 242 >>> BUILDING COMBINED MAP
Out[4]:
Make this Notebook Trusted to load map: File -> Trust Notebook
In [5]:
import json
from pathlib import Path
import folium
import geopandas as gpd
import numpy as np
import pandas as pd
import pgeocode
from branca.element import Element
from folium.plugins import HeatMap
# =====================================================
# 0. CONFIG & FILE PATHS
# =====================================================
bound_path = Path("CAUTH_MAY_2025_EN_BSC_1271049543973882786.geojson")
lsoa_path = Path("Lower_layer_Super_Output_Areas_December_2021_Boundaries_EW_BSC_V4_-4299016806856585929.geojson")
fuel_excel_path = Path("Sub-regional_fuel_poverty_statistics_2023.xlsx")
imd_excel_path = Path("Deprivation_2025.xlsx")
brownfield_path = Path("brownfield-land.geojson")
police_dir = Path("police_data")
pipeline_xlsx = Path("Copy of Housing_Pipeline_Long_List_External_Hackathon.xlsx")
print(">>> STEP 1/4 – Build vulnerability (LSOA + brownfield) data")
# =====================================================
# 1. VULNERABILITY DATA (LSOA + BROWNFIELD)
# =====================================================
# 1.1 LCR boundary
bound_gdf = gpd.read_file(bound_path)
if bound_gdf.crs is not None and bound_gdf.crs.to_epsg() != 4326:
bound_gdf = bound_gdf.to_crs(epsg=4326)
lcr = bound_gdf[bound_gdf["CAUTH25NM"] == "Liverpool City Region"].copy()
if lcr.empty:
raise ValueError("Cannot find 'Liverpool City Region' in boundary file")
try:
centre = lcr.geometry.union_all().centroid
except AttributeError:
centre = lcr.geometry.unary_union.centroid
centre_lat, centre_lon = centre.y, centre.x
# 1.2 LSOA polygons
lsoa_gdf = gpd.read_file(lsoa_path)
if lsoa_gdf.crs is not None and lsoa_gdf.crs.to_epsg() != 4326:
lsoa_gdf = lsoa_gdf.to_crs(epsg=4326)
lsoa_lcr = gpd.sjoin(
lsoa_gdf, lcr[["geometry"]], how="inner", predicate="intersects"
).drop(columns=["index_right"])
print(f" -> LSOAs inside LCR: {len(lsoa_lcr)}")
valid_lsoa_codes = set(lsoa_lcr["LSOA21CD"].unique())
# 1.3 Fuel poverty
try:
fuel_df = pd.read_excel(fuel_excel_path, sheet_name="Table 4", header=2)
fuel_df = fuel_df.rename(
columns={
"LSOA Code": "LSOA21CD",
"Proportion of households fuel poor (%)": "Fuel_Poverty_Rate",
}
)
fuel_df["Fuel_Poverty_Rate"] = pd.to_numeric(
fuel_df["Fuel_Poverty_Rate"], errors="coerce"
)
except Exception as e:
print(" !! Fuel data problem:", e)
fuel_df = pd.DataFrame(columns=["LSOA21CD", "Fuel_Poverty_Rate"])
# 1.4 IMD (decile)
try:
imd_df = pd.read_excel(imd_excel_path, sheet_name="IMD25", header=0)
imd_decile_col = next(
c for c in imd_df.columns if "IMD" in str(c) and "Decile" in str(c)
)
imd_df = imd_df.rename(
columns={
"LSOA code (2021)": "LSOA21CD",
imd_decile_col: "IMD_Decile",
}
)
imd_df["IMD_Decile"] = pd.to_numeric(imd_df["IMD_Decile"], errors="coerce")
except Exception as e:
print(" !! IMD data problem:", e)
imd_df = pd.DataFrame(columns=["LSOA21CD", "IMD_Decile"])
# 1.5 Police data: street crime + stop-and-search (uses ./police_data)
street_counts = {}
stop_search_points = []
all_csvs = list(police_dir.rglob("*.csv"))
for csv_file in all_csvs:
fname = csv_file.name.lower()
try:
if "street" in fname:
df = pd.read_csv(csv_file, usecols=["LSOA code"])
df = df[df["LSOA code"].isin(valid_lsoa_codes)]
counts = df["LSOA code"].value_counts()
for code, count in counts.items():
street_counts[code] = street_counts.get(code, 0) + count
elif "stop-and-search" in fname:
df = pd.read_csv(csv_file, usecols=["Latitude", "Longitude"])
df = df.dropna(subset=["Latitude", "Longitude"])
# Rough bounding box for LCR area
mask = df["Latitude"].between(53.0, 54.0) & df["Longitude"].between(-3.5, -2.0)
df_trimmed = df[mask]
if not df_trimmed.empty:
stop_search_points.append(df_trimmed)
except Exception:
continue
df_street = pd.DataFrame(list(street_counts.items()), columns=["LSOA21CD", "Crime_Count"])
df_stop_agg = pd.DataFrame(columns=["LSOA21CD", "StopSearch_Count"])
if stop_search_points:
all_stops = pd.concat(stop_search_points, ignore_index=True)
gdf_stops = gpd.GeoDataFrame(
all_stops,
geometry=gpd.points_from_xy(all_stops.Longitude, all_stops.Latitude),
crs="EPSG:4326",
)
stops_with_lsoa = gpd.sjoin(
gdf_stops, lsoa_lcr[["LSOA21CD", "geometry"]], predicate="within"
)
stop_counts = stops_with_lsoa["LSOA21CD"].value_counts().reset_index()
stop_counts.columns = ["LSOA21CD", "StopSearch_Count"]
df_stop_agg = stop_counts
# 1.6 Merge metrics into master LSOA GeoDataFrame
master_gdf = lsoa_lcr.merge(fuel_df, on="LSOA21CD", how="left")
master_gdf = master_gdf.merge(imd_df, on="LSOA21CD", how="left")
master_gdf = master_gdf.merge(df_street, on="LSOA21CD", how="left")
master_gdf = master_gdf.merge(df_stop_agg, on="LSOA21CD", how="left")
master_gdf["Fuel_Poverty_Rate"] = master_gdf["Fuel_Poverty_Rate"].fillna(0)
master_gdf["IMD_Decile"] = master_gdf["IMD_Decile"].fillna(10)
master_gdf["Crime_Count"] = master_gdf["Crime_Count"].fillna(0)
master_gdf["StopSearch_Count"] = master_gdf["StopSearch_Count"].fillna(0)
# Normalisation
print(">>> STEP 2/4 – Normalising metrics")
f_min, f_max = master_gdf["Fuel_Poverty_Rate"].min(), master_gdf["Fuel_Poverty_Rate"].max()
master_gdf["norm_fuel"] = (master_gdf["Fuel_Poverty_Rate"] - f_min) / (
(f_max - f_min) + 1e-9
)
master_gdf["norm_imd"] = (11 - master_gdf["IMD_Decile"]) / 10.0 # 1 = most deprived
c_max = master_gdf["Crime_Count"].quantile(0.95)
if c_max <= 0:
master_gdf["norm_crime"] = 0
else:
master_gdf["norm_crime"] = (master_gdf["Crime_Count"] / c_max).clip(upper=1.0)
s_max = master_gdf["StopSearch_Count"].quantile(0.95)
if s_max <= 0:
master_gdf["norm_stop"] = 0
else:
master_gdf["norm_stop"] = (master_gdf["StopSearch_Count"] / s_max).clip(upper=1.0)
cols_round = ["norm_fuel", "norm_imd", "norm_crime", "norm_stop", "Fuel_Poverty_Rate"]
master_gdf[cols_round] = master_gdf[cols_round].round(3)
print(" -> Simplifying geometries")
master_gdf.geometry = master_gdf.geometry.simplify(tolerance=0.0001, preserve_topology=True)
# 1.7 Brownfield data
print(">>> STEP 3/4 – Preparing brownfield sites")
brownfield = gpd.read_file(brownfield_path).to_crs(epsg=4326)
bf_joined = gpd.sjoin(
brownfield, master_gdf[["geometry", "LSOA21CD"]], predicate="within"
)
bf_joined["hectares"] = pd.to_numeric(bf_joined["hectares"], errors="coerce").fillna(0)
bf_data_list = []
for _, row in bf_joined.iterrows():
if row.geometry.is_empty:
continue
bf_data_list.append(
{
"lat": row.geometry.centroid.y,
"lng": row.geometry.centroid.x,
"ha": round(row["hectares"], 2),
"name": row.get("name", row.get("SiteName", "Unknown Site")),
"address": row.get("site-address", "No address provided"),
"status": row.get("planning-permission-status", "N/A"),
"dwellings": row.get("minimum-net-dwellings", "N/A"),
"lsoa": row["LSOA21CD"],
}
)
geojson_str = master_gdf.to_json()
# =====================================================
# 2. PIPELINE PROJECTS (BUBBLES + HEATMAPS)
# =====================================================
print(">>> STEP 4/4 – Loading housing pipeline")
df_pipe = pd.read_excel(pipeline_xlsx)
cols_pipe = df_pipe.columns.tolist()
def pick_col(substring: str) -> str:
for c in cols_pipe:
if substring.lower() in c.lower():
return c
raise ValueError(f"Column containing '{substring}' not found")
ref_col = pick_col("Ref")
proj_name_col = pick_col("Project Name")
sponsor_col = pick_col("Sponsor")
la_col = pick_col("LA")
stage_col = pick_col("Pipeline First Cut")
postcode_col = pick_col("Postcode")
dev_col = pick_col("Developer")
summary_col = pick_col("One Line Summary")
units_col = pick_col("Number Of Units")
size_col = pick_col("Site Size")
pipeline = df_pipe[
[
ref_col,
proj_name_col,
sponsor_col,
la_col,
stage_col,
postcode_col,
dev_col,
summary_col,
units_col,
size_col,
]
].copy()
pipeline = pipeline.rename(
columns={
ref_col: "ref",
proj_name_col: "project_name",
sponsor_col: "sponsor",
la_col: "la",
stage_col: "pipeline_stage",
postcode_col: "postcode",
dev_col: "developer",
summary_col: "summary",
units_col: "units",
size_col: "site_size_ha",
}
)
pipeline["units"] = pd.to_numeric(pipeline["units"], errors="coerce")
pipeline["site_size_ha"] = pd.to_numeric(pipeline["site_size_ha"], errors="coerce")
# Geocode postcodes with pgeocode
nomi = pgeocode.Nominatim("GB")
def geocode_one(pc: str):
if pd.isna(pc):
return np.nan, np.nan
pc = str(pc).strip()
if not pc:
return np.nan, np.nan
rec = nomi.query_postal_code(pc)
lat = rec.get("latitude", np.nan)
lon = rec.get("longitude", np.nan)
return lat, lon
pipeline["postcode_clean"] = pipeline["postcode"].astype(str).str.strip()
pipeline[["lat", "lon"]] = pipeline["postcode_clean"].apply(
lambda x: pd.Series(geocode_one(x))
)
pipeline_geo = pipeline.dropna(subset=["lat", "lon"]).reset_index(drop=True)
print(" -> Geocoded sites:", len(pipeline_geo))
# Bands
units_bins = [0, 20, 50, 100, 200, np.inf]
units_labels = ["0–20 homes", "21–50 homes", "51–100 homes", "101–200 homes", "200+ homes"]
pipeline_geo["units_band"] = pd.cut(
pipeline_geo["units"].fillna(0),
bins=units_bins,
labels=units_labels,
include_lowest=True,
)
size_bins = [0, 0.5, 2, 5, 10, np.inf]
size_labels = ["<0.5 ha", "0.5–2 ha", "2–5 ha", "5–10 ha", "10+ ha"]
pipeline_geo["size_band"] = pd.cut(
pipeline_geo["site_size_ha"].fillna(0),
bins=size_bins,
labels=size_labels,
include_lowest=True,
)
# =====================================================
# 3. BUILD ONE COMBINED MAP
# =====================================================
print(">>> BUILDING COMBINED MAP")
m = folium.Map(
location=[centre_lat, centre_lon],
zoom_start=11,
tiles="CartoDB positron"
)
# Custom panes so polygons are under points, etc.
folium.map.CustomPane("poly_pane", z_index=400).add_to(m)
folium.map.CustomPane("brownfield_pane", z_index=650).add_to(m)
folium.map.CustomPane("projects_pane", z_index=700).add_to(m)
# -----------------------------------------------------
# 3.1 Pipeline project bubbles (FeatureGroups by units)
# -----------------------------------------------------
stage_colors = {"Short": "green", "Medium": "orange", "Long": "red"}
size_colors = {
"<0.5 ha": "#999999",
"0.5–2 ha": "#377eb8",
"2–5 ha": "#4daf4a",
"5–10 ha": "#984ea3",
"10+ ha": "#e41a1c",
}
fg_by_units = {
lab: folium.FeatureGroup(name=f"Projects: {lab}", show=True)
for lab in units_labels
}
for _, row in pipeline_geo.iterrows():
ub = row["units_band"]
if pd.isna(ub):
continue
ub = str(ub)
fg = fg_by_units[ub]
units = row["units"] if not np.isnan(row["units"]) else 0
radius = 4 + (np.sqrt(units) if units > 0 else 0)
stage = str(row["pipeline_stage"]).strip()
fill_color = stage_colors.get(stage, "blue")
sb = row["size_band"]
border_color = size_colors.get(sb, "#333333")
desc = (
f"<b>{row['project_name']}</b><br>"
f"{int(units) if units > 0 else 'Unknown'} homes in {row['la']}<br>"
f"Stage: {row['pipeline_stage']} | Site size: {row['site_size_ha']} ha ({sb})<br>"
f"Developer: {row['developer']}<br>"
f"Postcode: {row['postcode']}<br><br>"
f"{row['summary']}"
)
folium.CircleMarker(
location=[row["lat"], row["lon"]],
radius=radius,
color=border_color,
weight=2,
fill=True,
fill_opacity=0.85,
fill_color=fill_color,
tooltip=folium.Tooltip(desc),
pane="projects_pane",
).add_to(fg)
for fg in fg_by_units.values():
fg.add_to(m)
# -----------------------------------------------------
# 3.2 Heatmaps – units & site size
# -----------------------------------------------------
units_data = pipeline_geo[["lat", "lon", "units"]].dropna()
if not units_data.empty:
units_data = units_data[units_data["units"] > 0].copy()
units_data["w"] = units_data["units"] / units_data["units"].max()
fg_units_heat = folium.FeatureGroup(name="Heat: number of homes", show=False)
HeatMap(
data=list(zip(units_data["lat"], units_data["lon"], units_data["w"])),
radius=25,
blur=18,
min_opacity=0.25,
max_zoom=12,
).add_to(fg_units_heat)
fg_units_heat.add_to(m)
size_data = pipeline_geo[["lat", "lon", "site_size_ha"]].dropna()
if not size_data.empty:
size_data = size_data[size_data["site_size_ha"] > 0].copy()
size_data["w"] = size_data["site_size_ha"] / size_data["site_size_ha"].max()
fg_size_heat = folium.FeatureGroup(name="Heat: site size (ha)", show=False)
HeatMap(
data=list(zip(size_data["lat"], size_data["lon"], size_data["w"])),
radius=25,
blur=18,
min_opacity=0.25,
max_zoom=12,
).add_to(fg_size_heat)
fg_size_heat.add_to(m)
# -----------------------------------------------------
# 3.3 Inject custom UI for FOUR separate choropleths
# + Brownfield toggle
# -----------------------------------------------------
custom_ui = f"""
<style>
#controls {{
position: absolute; bottom: 30px; right: 10px; width: 320px;
background: rgba(255, 255, 255, 0.96); backdrop-filter: blur(5px);
padding: 15px; border-radius: 10px; box-shadow: 0 4px 15px rgba(0,0,0,0.25);
z-index: 1000; font-family: 'Segoe UI', Tahoma, Geneva, Verdana, sans-serif;
}}
#controls h4 {{ margin: 0 0 10px 0; color: #222; }}
.metric-buttons {{
display: grid;
grid-template-columns: repeat(2, 1fr);
grid-gap: 6px;
margin-bottom: 8px;
}}
.metric-btn {{
padding: 6px 8px;
border-radius: 6px;
border: 1px solid #ddd;
background: #f7f7f7;
cursor: pointer;
font-size: 12px;
font-weight: 600;
color: #444;
}}
.metric-btn.active {{
background: #222;
color: #fff;
border-color: #222;
}}
.legend-gradient {{
height: 10px; background: linear-gradient(to right, #ffffff, #fee5d9, #de2d26, #a50f15);
border-radius: 5px; margin-top: 8px; border: 1px solid #ccc;
}}
.legend-labels {{ display: flex; justify-content: space-between; font-size: 10px; color: #666; margin-top: 2px; }}
.popup-table td {{ padding: 2px 5px; border-bottom: 1px solid #eee; }}
.popup-header {{ margin: 0 0 5px 0; border-bottom: 2px solid #555; padding-bottom: 3px; font-size: 14px; color: #333; }}
</style>
<div id="controls">
<h4>Neighbourhood Vulnerability</h4>
<div class="metric-buttons">
<button class="metric-btn active" data-metric="none">No Choropleth</button>
<button class="metric-btn" data-metric="fuel">Fuel Poverty</button>
<button class="metric-btn" data-metric="imd">IMD Deprivation</button>
<button class="metric-btn" data-metric="crime">Crime Density</button>
<button class="metric-btn" data-metric="stop">Stop & Search</button>
</div>
<div style="font-size:11px;color:#555;margin-bottom:4px;">
Showing: <b><span id="active_metric_label">None</span></b>
</div>
<div style="font-size:11px;color:#555;margin:4px 0 6px 0;">
<label>
<input type="checkbox" id="bf_toggle" checked />
Show brownfield sites
</label>
</div>
<div class="legend-gradient"></div>
<div class="legend-labels"><span>Lower</span><span>Higher</span></div>
</div>
<script>
// Data from Python
var lsoaData = {geojson_str};
var brownfieldData = {json.dumps(bf_data_list)};
var mapInstance = null;
var geoLayers = {{}};
var bfLayerGroup = null;
// ------------ Colour scales for each metric ------------
function getColorFuel(v) {{
if (isNaN(v)) return '#cccccc';
if (v > 0.85) return '#cb181d';
if (v > 0.6) return '#fb6a4a';
if (v > 0.3) return '#fcae91';
if (v > 0.0) return '#fee5d9';
return '#ffffff';
}}
function getColorIMD(v) {{
if (isNaN(v)) return '#cccccc';
if (v > 0.85) return '#54278f';
if (v > 0.6) return '#756bb1';
if (v > 0.3) return '#9e9ac8';
if (v > 0.0) return '#dadaeb';
return '#ffffff';
}}
function getColorCrime(v) {{
if (isNaN(v)) return '#cccccc';
if (v > 0.85) return '#08519c';
if (v > 0.6) return '#3182bd';
if (v > 0.3) return '#6baed6';
if (v > 0.0) return '#deebf7';
return '#ffffff';
}}
function getColorStop(v) {{
if (isNaN(v)) return '#cccccc';
if (v > 0.85) return '#006d2c';
if (v > 0.6) return '#31a354';
if (v > 0.3) return '#74c476';
if (v > 0.0) return '#e5f5e0';
return '#ffffff';
}}
function findFoliumMap() {{
for (var key in window) {{
if (window[key] instanceof L.Map) return window[key];
}}
return null;
}}
// ------------ Initialise map overlays ------------
function initMap() {{
mapInstance = findFoliumMap();
if (!mapInstance) {{
setTimeout(initMap, 200);
return;
}}
function onEachLSOA(feature, layer) {{
var p = feature.properties;
var content = `
<div style="font-family:sans-serif;font-size:12px;min-width:220px;">
<h4 class="popup-header">${{p.LSOA21NM}}</h4>
<table class="popup-table" style="width:100%;border-collapse:collapse;">
<tr><td style="color:#666;">Code</td><td>${{p.LSOA21CD}}</td></tr>
<tr><td>Fuel Poverty</td><td><b>${{p.Fuel_Poverty_Rate}}%</b> <small>(${{p.norm_fuel}})</small></td></tr>
<tr><td>IMD Decile</td><td><b>${{p.IMD_Decile}}</b> <small>(${{p.norm_imd}})</small></td></tr>
<tr><td>Crimes</td><td><b>${{p.Crime_Count}}</b> <small>(${{p.norm_crime}})</small></td></tr>
<tr><td>Stop & Search</td><td><b>${{p.StopSearch_Count}}</b> <small>(${{p.norm_stop}})</small></td></tr>
</table>
</div>
`;
layer.bindPopup(content);
layer.bindTooltip(p.LSOA21NM, {{sticky:true}});
}}
// Create four separate GeoJSON layers, one per metric
geoLayers['fuel'] = L.geoJson(lsoaData, {{
pane: 'poly_pane',
style: function(feature) {{
var v = feature.properties.norm_fuel || 0;
return {{
fillColor: getColorFuel(v),
weight: 0.6,
color: '#777',
opacity: 0.6,
fillOpacity: 0.7
}};
}},
onEachFeature: onEachLSOA
}});
geoLayers['imd'] = L.geoJson(lsoaData, {{
pane: 'poly_pane',
style: function(feature) {{
var v = feature.properties.norm_imd || 0;
return {{
fillColor: getColorIMD(v),
weight: 0.6,
color: '#777',
opacity: 0.6,
fillOpacity: 0.7
}};
}},
onEachFeature: onEachLSOA
}});
geoLayers['crime'] = L.geoJson(lsoaData, {{
pane: 'poly_pane',
style: function(feature) {{
var v = feature.properties.norm_crime || 0;
return {{
fillColor: getColorCrime(v),
weight: 0.6,
color: '#777',
opacity: 0.6,
fillOpacity: 0.7
}};
}},
onEachFeature: onEachLSOA
}});
geoLayers['stop'] = L.geoJson(lsoaData, {{
pane: 'poly_pane',
style: function(feature) {{
var v = feature.properties.norm_stop || 0;
return {{
fillColor: getColorStop(v),
weight: 0.6,
color: '#777',
opacity: 0.6,
fillOpacity: 0.7
}};
}},
onEachFeature: onEachLSOA
}});
// Brownfield points: ALWAYS BROWN (toggled by checkbox)
bfLayerGroup = L.layerGroup();
brownfieldData.forEach(function(site) {{
var circle = L.circleMarker([site.lat, site.lng], {{
pane: 'brownfield_pane',
radius: 4 + (site.ha * 0.4),
fillColor: '#8c510a', // brown
color: '#4d2600',
weight: 1,
opacity: 1,
fillOpacity: 0.95
}});
var bfContent = `
<div style="font-family:sans-serif;font-size:12px;min-width:200px;">
<h4 class="popup-header">${{site.name}}</h4>
<div style="margin-bottom:8px;color:#555;">${{site.address}}</div>
<table class="popup-table" style="width:100%;">
<tr><td><strong>Size:</strong></td><td>${{site.ha}} ha</td></tr>
<tr><td><strong>Dwellings:</strong></td><td>${{site.dwellings}}</td></tr>
<tr><td><strong>Status:</strong></td><td>${{site.status}}</td></tr>
</table>
</div>
`;
circle.bindPopup(bfContent);
bfLayerGroup.addLayer(circle);
}});
setupButtons();
}}
// ------------ Brownfield visibility toggle ------------
function updateBrownfieldVisibility() {{
if (!bfLayerGroup || !mapInstance) return;
var toggle = document.getElementById('bf_toggle');
if (!toggle) return;
if (toggle.checked) {{
if (!mapInstance.hasLayer(bfLayerGroup)) {{
bfLayerGroup.addTo(mapInstance);
}}
}} else {{
if (mapInstance.hasLayer(bfLayerGroup)) {{
mapInstance.removeLayer(bfLayerGroup);
}}
}}
}}
// ------------ Button logic: toggle which choropleth is visible ------------
function setActiveMetric(metric) {{
// Remove all metric layers
Object.keys(geoLayers).forEach(function(key) {{
var layer = geoLayers[key];
if (mapInstance.hasLayer(layer)) {{
mapInstance.removeLayer(layer);
}}
}});
// Update active button class
var buttons = document.querySelectorAll('#controls .metric-btn');
buttons.forEach(function(btn) {{
btn.classList.toggle('active', btn.getAttribute('data-metric') === metric);
}});
// Update label
var label = document.getElementById('active_metric_label');
var mapping = {{
'none': 'None',
'fuel': 'Fuel Poverty',
'imd': 'IMD Deprivation',
'crime': 'Crime Density',
'stop': 'Stop & Search'
}};
label.textContent = mapping[metric] || 'None';
// If "none" selected, just leave polygons off
if (metric === 'none') {{
return;
}}
// Add the chosen layer
var chosen = geoLayers[metric];
if (chosen) {{
chosen.addTo(mapInstance);
}}
}}
function setupButtons() {{
var buttons = document.querySelectorAll('#controls .metric-btn');
buttons.forEach(function(btn) {{
btn.addEventListener('click', function() {{
var metric = this.getAttribute('data-metric');
setActiveMetric(metric);
}});
}});
var bfToggle = document.getElementById('bf_toggle');
if (bfToggle) {{
bfToggle.addEventListener('change', updateBrownfieldVisibility);
}}
// start with "none" but brownfield ON
setActiveMetric('none');
updateBrownfieldVisibility();
}}
initMap();
</script>
"""
m.get_root().html.add_child(Element(custom_ui))
# Standard layer control for project + heatmap overlays
folium.LayerControl(collapsed=False).add_to(m)
# Save to HTML and display in notebook
m.save("LCR_vulnerability_pipeline_four_choropleths.html")
m
>>> STEP 1/4 – Build vulnerability (LSOA + brownfield) data -> LSOAs inside LCR: 1040 >>> STEP 2/4 – Normalising metrics -> Simplifying geometries >>> STEP 3/4 – Preparing brownfield sites >>> STEP 4/4 – Loading housing pipeline -> Geocoded sites: 242 >>> BUILDING COMBINED MAP
Out[5]:
Make this Notebook Trusted to load map: File -> Trust Notebook
Housing Vulnerability & Development Map – Liverpool City Region¶
This map brings together two stories on one canvas:
- How vulnerable each neighbourhood (LSOA) is, based on deprivation and policing data.
- Where future housing is planned, and how those sites sit inside that vulnerability context.
The aim is simple: help planners, councils and housing partners see where new homes could either help or accidentally deepen existing inequalities.
What the coloured polygons show – neighbourhood vulnerability¶
Each polygon is a Lower Super Output Area (LSOA) within the Liverpool City Region.
I use four different indicators, each available as its own choropleth:
Fuel Poverty (Sub-regional Fuel Poverty Stats 2023)
Percentage of households that are fuel poor in each LSOA.- Why it matters: fuel poverty is a direct signal of financial stress and poor housing quality (poor insulation, old heating systems).
- How to read it: darker areas = higher share of households struggling to heat their home.
IMD Deprivation (Index of Multiple Deprivation 2025)
LSOA-level IMD decile, flipped so that darker colours represent more deprivation.- Why it matters: IMD brings together income, employment, health, education, crime and environment. It’s a good “all-round” vulnerability signal.
- How to read it: darker areas = more deprived; lighter = relatively less deprived.
Crime Density (Police “street” crime, 2022–2025)
Count of recorded street-level crimes, aggregated to LSOA and normalised, with very high outliers trimmed at the 95th percentile.- Why it matters: crime and fear of crime strongly influence perceived safety, willingness to walk, and the attractiveness of new homes.
- How to read it: darker areas = higher crime intensity given the local police data; pale areas have fewer recorded incidents.
Stop & Search Intensity (Police “stop-and-search” points, 2022–2025)
Each stop-and-search event is mapped and spatially joined back to its LSOA, then converted into a normalised rate.- Why it matters: frequent stop & search can signal policing pressure and tensions with communities, often aligned with deprivation and inequality. It gives an extra lens on vulnerability that is different from recorded crime.
- How to read it: darker areas = more stop & search events; lighter areas = fewer encounters.
You can switch between these four lenses using the control panel on the right.
There is also a “No choropleth” option which clears the shading so you can focus purely on the brownfield and housing sites.
What the brown circles show – brownfield land¶
The brown circles represent brownfield sites (from the brownfield-land register) that fall inside the LCR LSOAs:
- Location: circle position is the centroid of each brownfield polygon.
- Size of circle: proportional to site area in hectares – larger circles = bigger sites.
- Popup details: site name, address, planning status, and estimated dwellings where available.
Why use brownfield as a key layer?
- Brownfield land is where government and councils often want to direct growth first, to avoid eating into greenfield land.
- Many of these sites sit right in the middle of high-need communities (fuel poor, highly deprived, higher crime).
Mapping them against the vulnerability measures shows where new homes could have the biggest regeneration impact, but also where extra support on safety, services and infrastructure is essential. - The brownfield toggle lets you hide or show these sites, so you can read the vulnerability pattern on its own before overlaying potential development opportunities.
What the circles with outlines show – housing pipeline projects¶
On top of the vulnerability and brownfield layers, the map adds future housing pipeline sites from the LCR spreadsheet:
- Each circle represents one housing project.
- Radius grows with the number of homes (units) – larger circles = larger schemes.
- Circle fill colour reflects pipeline stage:
- Short term
- Medium term
- Long term
- Circle border colour reflects site size band (in hectares), so you can quickly distinguish between small infill plots and strategic large sites.
- Tooltip and popup text summarise:
- Project name
- Local authority
- Units and site size
- Developer and postcode
- One-line project summary
Why combine pipeline data with vulnerability indicators?
- It reveals where the region is already planning to build in relation to high-need areas:
- Are large, short-term schemes falling mainly in deprived LSOAs or in relatively affluent ones?
- Are high-crime or high stop-and-search neighbourhoods seeing major housing growth without clear evidence of parallel investment in safety, transport or services?
- It helps spot gaps:
- Highly deprived areas with very little pipeline activity may need targeted programmes or policy attention.
- Brownfield clusters inside vulnerable neighbourhoods that are not yet in the pipeline could be candidates for future schemes.
Heatmaps – intensity of housing growth¶
Two optional heatmap layers summarise the overall intensity of development:
Heat: number of homes
- Takes all pipeline sites, weights them by units, and creates a smooth “hotspot” surface.
- Hotter patches show where the largest concentration of new homes is planned.
Heat: site size (ha)
- Weights sites by physical area instead of units.
- Highlights where the largest land footprints are being brought forward, even if unit numbers are modest (e.g. low-density schemes or mixed-use sites).
These layers are useful if you want a regional overview of growth pressure, rather than thinking in terms of individual projects.
Why these specific indices for housing decisions?¶
Bringing these particular datasets together is deliberate:
- Fuel poverty: connects housing to the cost of living and thermal comfort. A new scheme here could either relieve pressure (through high-quality, efficient homes) or worsen it if delivered at unaffordable rents or poor standard.
- IMD: captures structural disadvantage across work, health, education and environment. It helps prioritise where housing should be part of a wider regeneration package, not just extra units.
- Crime density: speaks to actual and perceived safety. Even a beautifully designed development will struggle if residents don’t feel safe walking to the shops, schools, or bus stops.
- Stop & search: adds a social justice lens. High stop-and-search rates can indicate policing pressure and strained community relations, which matter when planning engagement, design, and support services for new residents.
- Brownfield sites + pipeline projects: root the whole analysis in concrete development decisions – not just abstract vulnerability scores.
Together, the map lets decision-makers ask place-based questions like:
- Where are we planning thousands of new homes in areas already under pressure from deprivation and crime?
- Which brownfield clusters overlap with high need but currently have no pipeline schemes?
- Are there LSOAs that are relatively less deprived, but absorbing a lot of new homes, potentially easing pressure on more vulnerable areas?
How to read and use this map¶
- Pick a vulnerability lens from the right-hand panel: Fuel Poverty, IMD, Crime, or Stop & Search.
- Toggle brownfield sites on or off to focus on development opportunities.
- Use the Layer Control (top-left) to:
- Switch pipeline bubble size bands on/off.
- Enable or disable the heatmaps.
- Hover and click on polygons and circles to view local detail for specific LSOAs and sites.
The result is an interactive planning tool that connects who is most vulnerable, where the land is, and where new homes are actually coming, in one view.
In [ ]: